from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template


def query_hld(ref_date, portf_list=None, tick_list=None):
	
	sql_tpl = Template('''
	  select hld.*
		from (select me.lticker as sec_ticker,
					 si."name" as sec_name,
					 me.security_type as sec_type,
					 decode(sign(me.amount),1,'LONG','SHORT') as ls,
					 me.amount as sec_position,
					 me.avg_cost_price as cost_local,
					 me.avg_cost_price / fx.price as cost_base,
					 me.mkt_price as price_local,
					 me.mkt_price / fx.price as price_base,
					 me.amount * me.mkt_price * me.point_value as val_local,
					 me.amount * me.mkt_price * me.point_value / fx.price as val_base,
					 me.amount * me.avg_cost_price * me.point_value / fx.price /
					 (pv.mkt_val_post - NVL(sr.amt,0)) as cost_exposure,
					 me.amount * round(me.mkt_price/fx.price,6)* me.point_value /
					 (pv.mkt_val_post - NVL(sr.amt,0)) as mkt_exposure,
					 me.amount * me.avg_cost_price * me.point_value / fx.price /
					 pv.mkt_val_post as cost_exposure_post,
					 me.amount * me.mkt_price * me.point_value / fx.price /
					 pv.mkt_val_post as mkt_exposure_post,
					 sign(me.amount) * (me.mkt_price / me.avg_cost_price - 1) as ret,
					 0.01 * pv.mkt_val_post / (me.mkt_price * me.point_value / fx.price) as one_pct_num,
					 me.price_currency as local_curncy,
					 pi.currency as base_curncy,
					 me.market as market,
					 me.portfolio_id as portfolio_id,
					 pi."name" as portf_name
				from mkt_expo me
				left join comm_security_static_info si on si.ticker = me.lticker
				left join portfolio_info pi on pi.portfolio_id = me.portfolio_id
				left join comm_fx_latest fx on fx.ref_date = me.ref_date
										   and fx.dom_curncy = me.price_currency
										   and fx.for_curncy = pi.currency
				left join portfolio_mkt_val pv on pv.ref_date = me.ref_date
													   and pv.portfolio_id = me.portfolio_id
				left join (select parent,ref_date,sum(decode(type,'subscription',1,-1)*amount) amt 
							from amount_chg_ex@ACCT_DB group by parent,ref_date) sr 
							on sr.parent=me.portfolio_id and sr.ref_date=me.ref_date
			   where me.ref_date = TO_DATE('${DATE}', 'yyyy-mm-dd')
				 and ${PORTF_CRITERIA}
				 and ${TICK_CRITERIA}) hld
		left join security_type_rank r on r.sec_type = hld.sec_type
	   order by hld.portfolio_id, r.rank, hld.val_base desc''')
	
	sql_text = sql_tpl.substitute(DATE=ref_date.isoformat(), 
								TICK_CRITERIA=format_sql_criteria('me.lticker', tick_list), 
								PORTF_CRITERIA=format_sql_criteria('me.portfolio_id', portf_list))

	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()
	cursor.execute(sql_text)
	return cursor.fetchall()
	